package cn.edu.niit.dao;

import cn.edu.niit.db.JDBCUtil;
import cn.edu.niit.javabean.Book;
import cn.edu.niit.javabean.Message;
import cn.edu.niit.javabean.User;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class AdminDao {
    public List<User> getAllUsers(String reader, String cellphone, String username, int pageNum, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from borrow_card where 1=1 ");
        List<Object> list = new ArrayList<>();
        //拼接sql语句 此时 可以用list把所有参数放进去 后面遍历创建set ？条件
        if (reader != null && !reader.equals("")) {
            sql.append("and reader like CONCAT('%',?,'%') ");
            list.add(reader);
        }
        if (cellphone != null && !cellphone.equals("")) {
            sql.append("and cellphone like CONCAT('%',?,'%') ");
            list.add(cellphone);
        }
        if (username != null && !username.equals("")) {
            sql.append("and username like CONCAT('%',?,'%') ");
            list.add(username);
        }
        sql.append("limit ?,?");
        list.add((pageNum - 1) * pageSize);
        list.add(pageSize);
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<User> users = new ArrayList<>();
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql.toString(), params);
            while (rs.next()) {
                User user = User.builder().username(rs.getString("username")).cellphone(rs.getString("cellphone"))
                        .describe(rs.getString("describe")).email(rs.getString("email")).reader(rs.getString("reader"))
                        .status(rs.getString("status"))
                        .build();
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }

    public int getAllUserCount(String reader, String cellphone, String username) {
        StringBuilder sql = new StringBuilder("select count(0) as countNum from borrow_card where 1=1 ");
        List<Object> list = new ArrayList<>();
        //拼接sql语句 此时 可以用list把所有参数放进去 后面遍历创建set ？条件
        if (reader != null && !reader.equals("")) {
            sql.append("and reader like CONCAT('%',?,'%') ");
            list.add(reader);
        }
        if (cellphone != null && !cellphone.equals("")) {
            sql.append("and cellphone like CONCAT('%',?,'%') ");
            list.add(cellphone);
        }
        if (username != null && !username.equals("")) {
            sql.append("and username like CONCAT('%',?,'%') ");
            list.add(username);
        }
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql.toString(), params);
            while (rs.next()) {
                return rs.getInt("countNum");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public List<Book> getBooks(String name, String author, String check, int pageNum, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from books where 1=1 ");
        List<Object> list = new ArrayList<>();
        //拼接sql语句 此时 可以用list把所有参数放进去 后面遍历创建set ？条件
        if (name != null && !name.equals("")) {
            sql.append("and name like CONCAT('%',?,'%') ");
            list.add(name);
        }
        if (author != null && !author.equals("")) {
            sql.append("and author like CONCAT('%',?,'%') ");
            list.add(author);
        }
        if (check != null) {
            sql.append("and status=0 ");
        }
        sql.append("limit ?,?");
        list.add((pageNum - 1) * pageSize);
        list.add(pageSize);
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<Book> books = new ArrayList<>();
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql.toString(), params);
            while (rs.next()) {
                Book book = Book.builder().id(rs.getString("id")).name(rs.getString("name")).author(rs.getString("author"))
                        .description(rs.getString("description")).status(rs.getInt("status")).build();
                books.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return books;
    }

    public int getBooksCount(String name, String author, String check) {
        StringBuilder sql = new StringBuilder("select count(0) as countNum from books where 1=1 ");
        List<Object> list = new ArrayList<>();
        //拼接sql语句 此时 可以用list把所有参数放进去 后面遍历创建set ？条件
        if (name != null && !name.equals("")) {
            sql.append("and name like CONCAT('%',?,'%') ");
            list.add(name);
        }
        if (author != null && !author.equals("")) {
            sql.append("and author like CONCAT('%',?,'%') ");
            list.add(author);
        }
        if (check != null) {
            sql.append("and status=0 ");
        }
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<Book> books = new ArrayList<>();
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql.toString(), params);
            while (rs.next()) {
                return rs.getInt("countNum");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int updatePassword(String username) {
        String sql = "update borrow_card set password='123456' where username=?";
        return  JDBCUtil.getInstance().executeUpdate(sql, new Object[]{username});
    }

    public int delUser(String username) {
        String sql = "delete from borrow_card where username=?";
        return JDBCUtil.getInstance().executeUpdate(sql, new Object[]{username});
    }

    public List<Message> getAllMessage(String username, int pageNum, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from message where 1=1 ");
        List<Object> list = new ArrayList<>();
        //拼接sql语句 此时 可以用list把所有参数放进去 后面遍历创建set ？条件

        if (username != null && !username.equals("")) {
            sql.append("and card_id like CONCAT('%',?,'%') ");
            list.add(username);
        }
        sql.append("order by public_date desc ");
        sql.append("limit ?,?");
        list.add((pageNum - 1) * pageSize);
        list.add(pageSize);
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<Message> messages = new ArrayList<>();
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql.toString(), params);
            while (rs.next()) {
                Message message = Message.builder().id(rs.getString("id")).publicDate(rs.getTimestamp("public_date"))
                        .detail(rs.getString("detail")).cardId(rs.getString("card_id")).build();
                messages.add(message);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messages;
    }

    public int getAllMessageCount(String username) {
        StringBuilder sql = new StringBuilder("select count(0) as count from message where 1=1 ");
        List<Object> list = new ArrayList<>();
        //拼接sql语句 此时 可以用list把所有参数放进去 后面遍历创建set ？条件

        if (username != null && !username.equals("")) {
            sql.append("and card_id like CONCAT('%',?,'%') ");
            list.add(username);
        }
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<Message> messages = new ArrayList<>();
        try {
            ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql.toString(), params);
            while (rs.next()) {
                return rs.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int delMessages(String id) {
        String sql = "delete from message where id=?";
        String[] split = id.split(",");
        int count = 0;
        for (String s : split) {
            count+=JDBCUtil.getInstance().executeUpdate(sql, new Object[]{s});
        }
        return count;
    }

    public int delBooks(String id) {
        String sql = "delete from books where id=?";
        String[] split = id.split(",");
        int count = 0;
        for (String s : split) {
            count+=JDBCUtil.getInstance().executeUpdate(sql, new Object[]{s});
        }
        return count;
    }
}
